import mysql.connector
from mysql.connector import Error
import pandas as pd
import tkinter as tk
from tkinter import filedialog
from datetime import datetime, timedelta
from collections import defaultdict


def generate_time_intervals(initial_time_str, interval_delta, num_intervals):
    initial_time = datetime.strptime(initial_time_str, "%Y-%m-%d %H:%M:%S")
    return [
        (initial_time + i * interval_delta).strftime("%Y-%m-%d %H:%M:%S")
        for i in range(num_intervals + 1)
    ]


# 构建查询的函数需要修改，以安全地处理表名
def build_query(table_name, device_ids, start_time, end_time):
    placeholders = ', '.join(['%s'] * len(device_ids))
    query = f"""
    SELECT
        device_id,
        SUM(CASE WHEN device_status = 0 THEN 1 ELSE 0 END) AS 'status是0',
        SUM(CASE WHEN device_status = 1 THEN 1 ELSE 0 END) AS 'status是1',
        SUM(CASE WHEN device_status > 1 AND device_status < 10 THEN 1 ELSE 0 END) AS 'status1~4之间',
        SUM(CASE WHEN device_status < 0 THEN 1 ELSE 0 END) AS 'status<0',
        SUM(CASE WHEN device_status > 9 THEN 1 ELSE 0 END) AS 'status>4'
    FROM
        {table_name}
    WHERE
        device_id IN ({placeholders})
            AND update_time >= %s
            AND update_time < %s
    GROUP BY
        device_id
    ORDER BY
        device_id;
    """
    # 注意：我们不在这里执行查询，而是返回查询字符串和参数列表
    params = device_ids + [start_time, end_time]
    return query, params
 


# 获取设备状态计数的函数也需要修改以接受查询字符串和参数
def fetch_device_status_counts(query, params):
    try:
        connection = mysql.connector.connect(
            host="192.168.3.167",
            database="gwza231dev",
            user="tester",
            password="tester1234",
        )
        if connection.is_connected():
            cursor = connection.cursor(dictionary=True)
            cursor.execute(query, params)
            result = cursor.fetchall()
            return result
    except Error as e:
        print("Error while connecting to MySQL", e)
        return None
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()


def select_output_file_path():
    root = tk.Tk()
    root.withdraw()
    file_path = filedialog.asksaveasfilename(
        defaultextension=".csv",
        filetypes=[("CSV files", "*.csv"), ("All files", "*.*")],
    )
    return file_path


csv_file_path = select_output_file_path()
start_time = "2025-04-09 17:22:00"
his_table = 'map_device_his0328'
# 按需配置时间参数
time_interval = timedelta(minutes=60)
total_duration = timedelta(hours=16)
num_intervals = total_duration // time_interval

time_list = generate_time_intervals(
    start_time,
    time_interval,
    num_intervals
)
print(time_list)

# device_ids = [
#     "GW15A0","GW15D0","GW1605","GW1686","GW1746","GW1869","GW1D59","GW1D89","GW4214","GW42A8","GW44F1",
#     "GW4526","GW46AF","GW4704","GW488D","GW492A","GW493A","GW4E9D","GW4EA2","GW5014","GW5110","GW522B","GW5297","GW52A8","GW52AD",
#     "GW530D","GW5318","GW533A","GW537A","GW5416","GW5502","GW5616","GW5686","GW568B","GW5691","GW5696","GW56A7","GW56DC","GW579E","GW5809","GW589C","GW58A1",
# ]"GW5110","GW4920","GW5318","GW1D89","GW56DC",,"GW534D"
device_ids = [
    "GW534D","GW537A","GW4526","GW58A1","GW1869","GW56DC","GW5110","GW5318","GW1D89",
    "GW4920","GW520A","GW1686","GW1D59","GW52AD","GW568B","GW589C","GW5054","GW493A",
    "GW56A7","GW4704","GW5691","GW4EA2","GW5416","GW44F1","GW1746","GW5696","GW5686",
    "GW46AF","GW5014","GW579E","GW5809","GW52A8","GW1829","GW1605",'GW15D0','GW5297',
    'GW4E9D','GW5502','GW15A0','GW533A','GW488D','GW42A8','GW522B','GW179E'
]

def process_devices_and_times(time_list, device_ids, his_table):
    all_results = []
    for i in range(len(time_list) - 1):
        start_time = time_list[i]
        end_time = time_list[i + 1]
        
        # 生成更简洁的时间段标识
        identifier = f"{start_time}-{end_time.split(' ')[1]}"
        
        query, params = build_query(his_table, device_ids, start_time, end_time)
        status_counts = fetch_device_status_counts(query, params)
        
        if status_counts:
            for row in status_counts:
                device_id = row['device_id']
                if isinstance(device_id, (bytes, bytearray)):
                    device_id = device_id.decode('utf-8')
                    # print(device_id)
                result_row = [
                    identifier,
                    device_id,
                    row.get('status是0', 0),
                    row.get('status是1', 0),
                    row.get('status1~4之间', 0),
                    row.get('status<0', 0),
                    row.get('status>4', 0)
                ]
                all_results.append(result_row)
    return all_results


try:
    results = process_devices_and_times(time_list, device_ids, his_table)
    columns = ['Time_Range', 'device_id', 'status是0', 'status是1', 'status1~4之间', 'status<0', 'status>4']
    df = pd.DataFrame(results,columns=columns)
    df.to_csv(csv_file_path, index=False)
    print(f"Results saved to {csv_file_path}")
except Exception as e:
    print(f"An error occurred: {e}")
